package com.pizza.database.mapping;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.pizza.abstrait.AbstractArticle;
import com.pizza.abstrait.AbstractCommande;
import com.pizza.abstrait.AbstractCommandeLigneArticle;
import com.pizza.abstrait.AbstractCommandeLigneFormule;
import com.pizza.abstrait.AbstractCommandeLignePizza;
import com.pizza.database.DBConnection;
import com.pizza.model.*;

/**
 * Class CommandeDB, mapping avec la bdd.
 *
 */
public class CommandeDB implements Serializable{

	private DBConnection dbConnexion;

	public CommandeDB(DBConnection _dbConnexion) {
		dbConnexion = _dbConnexion;
	}

	public int insertCommande(Commande _commande) {
		int retour = 0;

		Connection connexion = dbConnexion.getConnection();
		StringBuilder query = new StringBuilder();
		query.append("INSERT INTO commandes");
		query
				.append(" (id_clients, id_remises, id_adresselivraison, total, etat, tva, date, livraison)");
		query.append(" VALUES(?,?,?,?,?,?,?,?)");

		try {
			PreparedStatement pStatement = connexion.prepareStatement(query
					.toString());

			pStatement.setInt(1, _commande.getClient().getId());
			if(_commande.getRemise() != null) pStatement.setFloat(2, _commande.getRemise().getId());
			else pStatement.setFloat(2, 0);
			pStatement.setFloat(3, _commande.getAdresseLivraison().getId());
			pStatement.setFloat(4, _commande.getTotal());
			pStatement.setString(5, _commande.getEtat());
			pStatement.setFloat(6, _commande.getTva());
			pStatement.setTimestamp(7, new java.sql.Timestamp(_commande
					.getDate().getTime()));
			pStatement.setBoolean(8, _commande.isLivraison());

			// Insertion de la commande
			retour += pStatement.executeUpdate();
			
			ResultSet clefs = pStatement.getGeneratedKeys();
			
			if(clefs.next()){
				Long id = (Long)clefs.getObject(1);
			    _commande.setId(id.intValue());
			}
			clefs.close();
			pStatement.close();
			for(int i=0; i < _commande.getArticles().size(); i++) {
				insertCommandeArticle(_commande, _commande.getArticles().get(i));
			}
			for(int i=0; i < _commande.getPizzas().size(); i++) {
				insertCommandePizza(_commande, _commande.getPizzas().get(i));
			}
			for(int i=0; i < _commande.getFormules().size(); i++) {
				insertCommandeFormule(_commande, _commande.getFormules().get(i));
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return retour;
	}

	public int insertCommandeArticle(Commande _commande, AbstractCommandeLigneArticle _ligne) {
		int retour = 0;
		Connection connexion = dbConnexion.getConnection();
		StringBuilder query = new StringBuilder();
		query.append("INSERT INTO commandes_articles");
		query
				.append(" (id_commandes, id_articles, quantite, prix)");
		query.append(" VALUES(?,?,?,?)");

		try {
			PreparedStatement pStatement = connexion.prepareStatement(query
					.toString());

			pStatement.setInt(1, _commande.getId());
			pStatement.setInt(2, _ligne.getArticle().getId());
			pStatement.setInt(3, _ligne.getQuantite());
			pStatement.setFloat(4, _ligne.getPrix());
			
			// Insertion de la ligne commandeArticle
			retour += pStatement.executeUpdate();

			pStatement.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return retour;
	}
	
	public int insertCommandePizza(Commande _commande, AbstractCommandeLignePizza _ligne) {
		int retour = 0;
		Connection connexion = dbConnexion.getConnection();
		StringBuilder query = new StringBuilder();
		query.append("INSERT INTO commandes_pizzas");
		query
				.append(" (id_commandes, id_pizzas, id_pates, id_diametres, quantite, prix)");
		query.append(" VALUES(?,?,?,?,?,?)");

		try {
			PreparedStatement pStatement = connexion.prepareStatement(query
					.toString());

			pStatement.setInt(1, _commande.getId());
			pStatement.setInt(2, _ligne.getPizza().getId());
			if(_ligne.getPate() != null) pStatement.setInt(3, _ligne.getPate().getId());
			else pStatement.setInt(3, 0);
			pStatement.setInt(4, _ligne.getDiametre().getId());
			pStatement.setInt(5, _ligne.getQuantite());
			pStatement.setFloat(6, _ligne.getPrix());
			
			// Insertion de la ligne commandePizza
			retour += pStatement.executeUpdate();

			pStatement.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return retour;
	}
	
	public int insertCommandeFormule(Commande _commande, AbstractCommandeLigneFormule _ligne) {
		int retour = 0;
		Connection connexion = dbConnexion.getConnection();
		StringBuilder query = new StringBuilder();
		query.append("INSERT INTO commandes_formules");
		query
				.append(" (id_commandes, id_formules, quantite, prix)");
		query.append(" VALUES(?,?,?,?)");

		try {
			PreparedStatement pStatement = connexion.prepareStatement(query
					.toString());

			pStatement.setInt(1, _commande.getId());
			pStatement.setInt(2, _ligne.getFormule().getId());
			pStatement.setInt(3, _ligne.getQuantite());
			pStatement.setFloat(4, _ligne.getPrix());
			
			// Insertion de la ligne commandeFormule
			retour += pStatement.executeUpdate();

			pStatement.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return retour;
	}
	
	public Commande getCommandeById(int _id) {
		Connection connexion = dbConnexion.getConnection();
		Commande retour = null;
		StringBuilder query = new StringBuilder();
		query.append("SELECT * ");
		query.append(" FROM commandes");
		query.append(" WHERE id=?");

		StringBuilder queryPizzas = new StringBuilder();
		queryPizzas.append("SELECT * ");
		queryPizzas.append(" FROM commandes_pizzas");
		queryPizzas.append(" WHERE id_commandes=?");

		StringBuilder queryArticles = new StringBuilder();
		queryArticles.append("SELECT * ");
		queryArticles.append(" FROM commandes_articles");
		queryArticles.append(" WHERE id_commandes=?");

		StringBuilder queryFormules = new StringBuilder();
		queryFormules.append("SELECT * ");
		queryFormules.append(" FROM commandes_formules");
		queryFormules.append(" WHERE id_commandes=?");
		try {
			PreparedStatement pStatement = connexion.prepareStatement(query
					.toString());
			pStatement.setInt(1, _id);
			ResultSet rs = pStatement.executeQuery();
			rs.first();

			int id_clients = rs.getInt("id_clients");
			int id_remises = rs.getInt("id_remises");
			int id_adresseLivraison = rs.getInt("id_adresselivraison");
			float total = rs.getFloat("total");
			String etat = rs.getString("etat");
			float tva = rs.getFloat("tva");
			Date date = rs.getDate("date");
			boolean livraison = rs.getBoolean("livraison");

			Remise remise = null;
			if(id_remises != 0 ) remise = new RemiseDB(dbConnexion).getRemiseById(id_remises);
			
			retour = new Commande(_id, new ClientDB(dbConnexion)
					.getClientById(id_clients), remise, new AdresseDB(dbConnexion).getAdresseById(id_adresseLivraison), total, etat, tva, date,
					livraison);

			pStatement.close();

			pStatement = connexion.prepareStatement(queryPizzas.toString());
			pStatement.setInt(1, _id);
			rs = pStatement.executeQuery();
			while (rs.next()) {

				int id_pizza = rs.getInt("id_pizzas");
				int id_pate = rs.getInt("id_pates");
				int id_diametre = rs.getInt("id_diametres");
				int quantite = rs.getInt("quantite");
				float prix = rs.getFloat("prix");

								
				CommandeLignePizza ligne = new CommandeLignePizza(new PizzaDB(
						dbConnexion).getPizzaById(id_pizza), new DiametreDB(
						dbConnexion).getDiametreById(id_diametre), quantite,
						prix, retour);
				retour.addPizza(ligne);
			}
			pStatement.close();

			pStatement = connexion.prepareStatement(queryArticles.toString());
			pStatement.setInt(1, _id);
			rs = pStatement.executeQuery();
			while (rs.next()) {

				int id_article = rs.getInt("id_articles");
				int quantite = rs.getInt("quantite");
				float prix = rs.getFloat("prix");

				CommandeLigneArticle ligne = new CommandeLigneArticle(
						new ArticleDB(dbConnexion).getArticleById(id_article),
						quantite, prix, retour);
				retour.addArticle(ligne);
			}
			pStatement.close();

			pStatement = connexion.prepareStatement(queryFormules.toString());
			pStatement.setInt(1, _id);
			rs = pStatement.executeQuery();
			while (rs.next()) {

				int id_formule = rs.getInt("id_formules");
				int quantite = rs.getInt("quantite");
				float prix = rs.getFloat("prix");

				CommandeLigneFormule ligne = new CommandeLigneFormule(
						new FormuleDB(dbConnexion).getFormuleById(id_formule),
						quantite, prix, retour);
				retour.addFormule(ligne);
			}
			pStatement.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return retour;
	}

}
